﻿using Dapper;
using Domain.Entity;
using Domain.Entity.bs;
using Domain.Entity.tr;
using Domain.IRepository;
using Microsoft.IdentityModel.Tokens;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class ProductRepository : IProductRepository
    {

        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //添加货品信息
        public async Task<int> AddProduct(Product product)
        {
            using (SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[bs_product](proc_code, proc_name, type_id, type_name, proc_model, instock_price, outstock_price, proc_sort, proc_status, is_datamanagement, proc_expirationdate, proc_Warningdays,proc_primary_unit, proc_primary_barcode,proc_min,proc_max, create_name, create_time, remark)
                              values('{product.proc_code}','{product.proc_name}',{product.type_id},'{product.type_name}','{product.proc_model}',{product.instock_price},{product.outstock_price},{product.proc_sort},{product.proc_status},{product.is_datamanagement},'{product.proc_expirationdate}',{product.proc_Warningdays},'{product.proc_primary_unit}',{product.proc_primary_barcode},{product.proc_min},{product.proc_max},'{product.create_name}','{product.create_time}','{product.remark}');
                              SELECT CAST(SCOPE_IDENTITY() AS INT);";
                return await sqlConnection.ExecuteScalarAsync<int>(sql, product);//返回当前数据的主键
            }
        }

        //删除货品信息
        public async Task<int> DeleteProduct(int id)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[bs_product] set is_delete=1 where proc_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //分页高级查询
        public async Task<FenYe<Product>> GetAdvancedProduct(FenYe<Product> fenYe, string? code, string? name, string? typeid, string? model, string? unit, string? startinprice, string? endinprice, string? startoutprice, string? endoutprice, int? is_datamanagement, int? status, int? startwarningdays, int? endwarningdays, int? whid, string? barcode, string? starttime, string? endtime, string? createname)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 如果有类型条件，获取该类型及其所有子类型的ID
                List<string> typeIds = new List<string>();
                if (!string.IsNullOrEmpty(typeid))
                {
                    string s = $@"WITH TypeTree AS (
                                    SELECT type_id FROM bs_product_type WHERE type_id = {typeid}
                                    UNION ALL
                                    SELECT t.type_id 
                                    FROM bs_product_type t
                                    INNER JOIN TypeTree tt ON t.type_pid = tt.type_id
                                )
                                SELECT type_id FROM TypeTree";
                    typeIds = sqlConnection.Query<string>(s).ToList();
                }

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "proc_id");
                param.Add("@tabelName", "bs_product");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and proc_code like '%{code}%'";
                if (!string.IsNullOrEmpty(name))
                    sqlparam += $" and proc_name like '%{name}%'";
                if (!string.IsNullOrEmpty(typeid))
                    sqlparam += $" and type_id in ('{string.Join("','", typeIds)}')";
                if (!string.IsNullOrEmpty(model))
                    sqlparam += $" and proc_model like '%{model}%'";
                if (!string.IsNullOrEmpty(unit))
                    sqlparam += $" and proc_primary_unit like '%{unit}%'";
                if (!string.IsNullOrEmpty(barcode))
                    sqlparam += $" and proc_primary_barcode like '%{barcode}%'";
                if (!string.IsNullOrEmpty(startinprice) || !string.IsNullOrEmpty(endinprice))
                    sqlparam += $" and  instock_price between '{startinprice}' and '{endinprice}'";
                if (!string.IsNullOrEmpty(startoutprice) || !string.IsNullOrEmpty(endoutprice))
                    sqlparam += $" and  outstock_price between '{startoutprice}' and '{endoutprice}'";
                if (is_datamanagement!=null)
                    sqlparam += $" and is_datamanagement ={is_datamanagement}";
                if (startwarningdays!=null||endwarningdays!=null)
                    sqlparam += $" and  proc_Warningdays between '{startwarningdays}' and '{endwarningdays}'";
                if (status != null && status != 0)
                    sqlparam += $" and  proc_status={status}";
                if (whid > 0)
                    sqlparam += $" and proc_id=(select proc_id from [dbo].[bs_stock] where wh_id={whid})";
                if (!string.IsNullOrEmpty(starttime) || !string.IsNullOrEmpty(endtime))
                    sqlparam += $" and  create_time between '{starttime}' and '{endtime}'";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and create_name like '%{createname}%'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Product> list = sqlConnection.Query<Product>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //查询所有货品信息
        public async Task<List<Product>> GetAllProduct()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select * from [dbo].[bs_product] where is_delete=0";
                return sqlConnection.Query<Product>(sql).ToList();
            }
        }


        //分页查询货品信息
        public async Task<FenYe<Product>> GetProduct(FenYe<Product> fenYe, string? name, bool? inventory, int? whid, string? type)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 如果有类型条件，获取该类型及其所有子类型的ID
                List<string> typeIds = new List<string>();
                if (!string.IsNullOrEmpty(type))
                {
                    string s = $@"WITH TypeTree AS (
                                    SELECT type_id FROM bs_product_type WHERE type_id = {type}
                                    UNION ALL
                                    SELECT t.type_id 
                                    FROM bs_product_type t
                                    INNER JOIN TypeTree tt ON t.type_pid = tt.type_id
                                )
                                SELECT type_id FROM TypeTree";
                    typeIds = sqlConnection.Query<string>(s).ToList();
                }

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "proc_id");
                param.Add("@tabelName", "bs_product");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(name))
                    sqlparam += $" and proc_name like '%{name}%'";
                if (!string.IsNullOrEmpty(type))
                    sqlparam += $" and type_id in ('{string.Join("','", typeIds)}')";
                if (inventory.HasValue && inventory.Value)
                    sqlparam += $" and proc_inventory>0";
                if (whid>0)
                    sqlparam += $" and proc_id=(select proc_id from [dbo].[bs_stock] where wh_id={whid})";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Product> list = sqlConnection.Query<Product>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }

        //根据id查询货品信息
        public async Task<Product> GetProductById(int id)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[bs_product] where proc_id={id}";
                return await sqlConnection.QueryFirstAsync<Product>(sql);
            }
        }


        //更新货品总库存与总金额
        public async Task<int> Updateinventory(Product product)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[bs_product] set proc_inventory+={product.proc_inventory},proc_inventory_money+='{product.proc_inventory_money}',proc_ky_inventory+={product.proc_ky_inventory} where proc_id={product.proc_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //更新货品总库存与总金额(出库)
        public async Task<int> UpdateinventoryJian(Product product)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[bs_product] set proc_inventory-={product.proc_inventory},proc_inventory_money-='{product.proc_inventory_money}',proc_ky_inventory-={product.proc_ky_inventory} where proc_id={product.proc_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改货品计划可用库存
        public async Task<int> Updateky(int id, int inventory)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[bs_product] set proc_ky_inventory={inventory} where proc_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改货品信息
        public async Task<int> UpdateProduct(Product product)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[bs_product] set proc_code='{product.proc_code}',proc_name='{product.proc_name}',type_id={product.type_id},type_name='{product.type_name}',proc_model='{product.proc_model}',instock_price='{product.instock_price}',outstock_price='{product.outstock_price}',proc_sort={product.proc_sort},proc_status={product.proc_status},is_datamanagement={product.is_datamanagement},proc_expirationdate='{product.proc_expirationdate}',
                                proc_Warningdays={product.proc_Warningdays},proc_primary_unit='{product.proc_primary_unit}',proc_primary_barcode='{product.proc_primary_barcode}',proc_min={product.proc_min},proc_max={product.proc_max},update_name='{product.update_name}',update_time='{product.update_time}',remark='{product.remark}' where proc_id={product.proc_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //修改货品信息状态（启用/停用）
        public async Task<int> UpdateStatus(int procid, int status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[bs_product] set proc_status={status} where proc_id={procid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        // 分页查询货品信息（通过货品ID数组）
        public async Task<FenYe<Product>> GetProductsByIds(
            FenYe<Product> fenYe,
            int[] proc_id,
            string? proc_code = null,
            string? proc_primary_unit = null)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建基础查询条件
                string sqlparam = "is_delete = 0";

                // 处理货品ID数组条件
                if (proc_id != null && proc_id.Length > 0)
                {
                    // 直接转换为逗号分隔的字符串，无需单引号
                    string ids = string.Join(",", proc_id);
                    sqlparam += $" AND proc_id IN ({ids})";
                }

                // 添加其他筛选条件
                if (!string.IsNullOrWhiteSpace(proc_code))
                {
                    sqlparam += $" AND proc_code LIKE '%{proc_code.Replace("'", "''")}%'";
                    sqlparam += $" OR proc_name LIKE '%{proc_code.Replace("'", "''")}%'";
                }

                if (!string.IsNullOrWhiteSpace(proc_primary_unit))
                {
                    sqlparam += $" AND proc_primary_unit = '{proc_primary_unit.Replace("'", "''")}'";
                }

                // 配置分页存储过程参数
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "proc_id");
                param.Add("@tabelName", "bs_product");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@where", sqlparam);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行分页存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Product> list = (await sqlConnection.QueryAsync<Product>(sql, param)).ToList();

                // 返回分页结果
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }

        public async Task<List<Product>> GetProductsByIds(int[] proc_ids)
        {
            // 如果传入空数组，直接返回空结果
            if (proc_ids == null || proc_ids.Length == 0)
                return new List<Product>();

            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 使用参数化IN查询防止SQL注入
                string sql = $@"
                    SELECT *
                    FROM [dbo].[bs_product] 
                    WHERE proc_id IN @proc_ids";

                // 直接传递数组参数，Dapper会自动展开为参数化查询
                var parameters = new { proc_ids };

                // 执行查询并返回结果
                return (await sqlConnection.QueryAsync<Product>(sql, parameters)).ToList();
            }
        }
    }
}
